﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Bxas.Core.Exceptions;

namespace Bxas.Core
{
    public class ConnectionManager
    {
        private SqlConnection mainConnection;
        private SqlTransaction transaction;
        private String connectionString;
        public bool isTransactional;
        private SqlCommand cmd;
        public ConnectionManager(bool isTransactional, String connectionString)
        {
            CreateConnection(connectionString);
            this.isTransactional = isTransactional;
        }

        public ConnectionManager(String connectionString)
        {
            CreateConnection(connectionString);
            isTransactional = false;
        }

        private void CreateConnection(String connectionString)
        {
            this.connectionString = connectionString;
            mainConnection = new SqlConnection(this.connectionString);
        }

        public void OpenConnection()
        {
            try
            {
                mainConnection.Open();
                if (isTransactional == true)
                    transaction = mainConnection.BeginTransaction();
            }
            catch (Exception ex)
            {
                throw new BxasCoreException(ex.Message, ex);
            }

        }
        public void CloseConnection(bool isSuccess)
        {
            if (isTransactional == true)
            {
                if (isSuccess == false)
                    transaction.Rollback();
                else
                    transaction.Commit();
            }
            mainConnection.Close();
        }

        public void getData(ref DataTable dtBuffer, string sSql, List<SqlParameter> parameters)
        {
            try
            {

                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                SqlDataAdapter da = new SqlDataAdapter(sSql, mainConnection);
                da.SelectCommand.Transaction = transaction;
                if (parameters != null)
                    da.SelectCommand.Parameters.AddRange(parameters.ToArray());
                da.Fill(dtBuffer);
                if (!isTransactional)
                    CloseConnection(true);

            }
            catch (Exception ex)
            {
                throw new BxasCoreException(ex.Message + "  ||  " + sSql, ex);
            }

        }     

        public void getData(DataSet dtBuffer, string sSql)
        {
            try
            {
                if (dtBuffer.Tables.Count <= 0)
                    dtBuffer.Tables.Add();
                DataTable dt = dtBuffer.Tables[0];
                getData(ref dt, sSql, null);
            }
            catch (BxasCoreException ex)
            {
                throw ex;
            }
        }
        public void getData(DataSet dtBuffer, string sSql, List<SqlParameter> parameters)
        {
            try
            {
                if (dtBuffer.Tables.Count <= 0)
                    dtBuffer.Tables.Add();
                DataTable dt = dtBuffer.Tables[0];
                getData(ref dt, sSql, parameters);
            }
            catch (BxasCoreException ex)
            {

                throw ex;
            }
        }
        public void getData(ref DataTable dtBuffer, SqlCommand cmd)
        {
            try
            {

                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();

                cmd.Connection = mainConnection;
                cmd.Transaction = transaction;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtBuffer);
                if (!isTransactional)
                    CloseConnection(true);

            }
            catch (Exception ex)
            {
                throw new BxasCoreException(ex.Message+"  ||  "+cmd.CommandText, ex) { HataTuru = EnumaHataTuru.sql };
            }

        }
        public bool execSQL(string sSQL, List<SqlParameter> parameters)
        {

            try
            {

                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                cmd = new SqlCommand(sSQL, mainConnection);
                cmd.Transaction = transaction;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                cmd.ExecuteNonQuery();
                if (!isTransactional)
                    CloseConnection(true);
                return true;
            }
            catch (Exception ex)
            {
                throw new BxasCoreException(ex.Message+"  ||  "+sSQL, ex);
            }
        }
        public bool execSQL(string sSQL, SqlCommand cmd)
        {
            try
            {

                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();

                cmd.Connection = mainConnection;
                cmd.CommandText = sSQL;

                cmd.Transaction = transaction;
                cmd.ExecuteNonQuery();
                if (!isTransactional)
                    CloseConnection(true);
                return true;

            }
            catch (Exception ex)
            {

                throw new BxasCoreException(ex.Message + "  ||  " + sSQL, ex);
            }
        }
        public int execSQLRowCount(string sSQL, List<SqlParameter> parameters)
        {
            try
            {

                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                cmd = new SqlCommand(sSQL, mainConnection);
                cmd.Transaction = transaction;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                if (!isTransactional)
                    CloseConnection(true);
                return cmd.ExecuteNonQuery();


            }
            catch (Exception ex)
            {

                throw new BxasCoreException(ex.Message + "  ||  " + sSQL, ex);
            }
        }

        public int TopluVeriIsleme(DataSet dataSet, string sqlCumlesi, string tabloAdi)
        {
            return TopluVeriIsleme(dataSet.Tables[tabloAdi], sqlCumlesi);
        }
        public int TopluVeriIsleme(DataSet dataSet, string sqlCumlesi)
        {
            return TopluVeriIsleme(dataSet.Tables[0], sqlCumlesi);
        }
        public int TopluVeriIsleme(DataSet dataSet)
        {
            return TopluVeriIsleme(dataSet.Tables[0], "SELECT * FROM " + dataSet.Tables[0].TableName);
        }
        public int TopluVeriIsleme(DataTable dataTable)
        {
            return TopluVeriIsleme(dataTable, "SELECT * FROM " + dataTable.TableName);
        }
        public int TopluVeriIsleme(DataTable dataTable, string sqlCumlesi)
        {
            int functionReturnValue = 0;
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                using (SqlDataAdapter DataAdapter = new SqlDataAdapter(sqlCumlesi, mainConnection))
                {
                    if (isTransactional) DataAdapter.SelectCommand.Transaction = transaction;
                    using (SqlCommandBuilder CommandBuilder = new SqlCommandBuilder(DataAdapter))
                    {
                        functionReturnValue = DataAdapter.Update(dataTable);
                    }
                }
                if (!isTransactional)
                    CloseConnection(true);
                else
                    CloseConnection(true);
            }
            catch (Exception ex)
            {

                throw new BxasCoreException(ex.Message + "  ||  " + sqlCumlesi, ex);
            }
            return functionReturnValue;
        }

        public void TopluVeriIslemeIDDondur(DataSet ds)
        {
            string sIDFieldName = "";
            foreach (DataColumn dc in ds.Tables[0].Columns)
            {
                if (dc.AutoIncrement == true)
                {
                    sIDFieldName = dc.Caption;
                    break;
                }
            }
            TopluVeriIslemeIDDondur(ds, sIDFieldName);
        }
        public void TopluVeriIslemeIDDondur(DataTable dt)
        {
            //string sIDFieldName = dt.TableName + "ID";
            string sIDFieldName = "";
            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.AutoIncrement == true)
                {
                    sIDFieldName = dc.Caption;
                    break;
                }
            }
            TopluVeriIslemeIDDondur(dt, sIDFieldName);
        }
        public void TopluVeriIslemeIDDondur(DataSet ds, string sIDFieldName)
        {
            string ssql = "SELECT * FROM " + ds.Tables[0].TableName;
            DataTable dt = ds.Tables[0];
            TopluVeriIslemeIDDondur(dt, ssql, sIDFieldName);
        }
        public void TopluVeriIslemeIDDondur(DataTable dt, string sIDFieldName)
        {
            string ssql = "SELECT * FROM " + dt.TableName;
            TopluVeriIslemeIDDondur(dt, ssql, sIDFieldName);
        }
        public int TopluVeriIslemeIDDondur(DataTable dt, string ssql, string sIDFieldName)
        {
            int functionReturnValue = 0;

            if (mainConnection.State == ConnectionState.Closed)
                OpenConnection();
            SqlDataAdapter adp = new SqlDataAdapter(ssql, mainConnection);
            SqlDataAdapter adptmp = new SqlDataAdapter(ssql, mainConnection);

            if (isTransactional)
                adp.SelectCommand.Transaction = transaction;
            try
            {
                SqlCommandBuilder cb = new SqlCommandBuilder(adp);
                SqlCommand cmd;
                cmd = cb.GetInsertCommand();
                cmd.CommandText += "; " + ssql + " Where " + sIDFieldName + "=@@IDENTITY";
                cmd.UpdatedRowSource = UpdateRowSource.Both;
                adptmp.InsertCommand = cmd;
                adptmp.UpdateCommand = cb.GetUpdateCommand();
                adptmp.DeleteCommand = cb.GetDeleteCommand();

                adptmp.Update(dt);
            }
            catch (Exception ex)
            {

                throw new BxasCoreException(ex.Message + "  ||  " + ssql, ex);
            }

            return functionReturnValue;
        }
        private bool checkForInjection(string sql)
        {
            string[] keys = { "insert ", "update ", "delete ", "drop ", "truncate " };
            int indexOfLen = 20;
            if (sql.Length < indexOfLen)
            {
                indexOfLen = sql.Length;
            }
            foreach (string key in keys)
            {
                if (sql.IndexOf(key, indexOfLen, sql.Length - indexOfLen, StringComparison.InvariantCultureIgnoreCase) >= 0)
                {
                    string msg = string.Format("SQL Injection\nKey = {0}\nSQL = {1}", key, sql);
                    new BxasCoreException(msg + "  ||  "+sql , new Exception(msg));
                    return false;
                    //System.Diagnostics.EventLog.WriteEntry("IKU", msg, System.Diagnostics.EventLogEntryType.Error);
                    //throw new Exception(string.Format("SQL Injection\nKey = {0}", key));
                }
            }

            return true;
        }
    }
}
